package databaseDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.RoleBean;
import bean.UserBean;

public class UserManagement {

	public int adduser(UserBean ub) throws Exception{
		Connection con = DataAccess.getConnection();  // 获取连接
		String sql = "insert into users values(?,?,?,?,?,?,?,?,?,?,?)";  // 编写sql语句
		PreparedStatement pst = null; // 创建预处理语句
		try{
			pst = con.prepareStatement(sql);  // 拿到预处理
			pst.setString(1, ub.getUserID());
			pst.setString(2, ub.getNickName());
			pst.setString(3, ub.getUserName());
			pst.setString(4, ub.getPassword());
			pst.setString(5, ub.getUserDescription());
			pst.setString(6, ub.getRoleID());
			pst.setString(7, ub.getUserCreateTime());
			pst.setString(8, ub.getLoginTime());
			pst.setString(9, ub.getLoginTimeLong());
			pst.setString(10, ub.getCurrentStatus());
			pst.setString(11, ub.getIP());
			//pst.setString(5, ub.getRoleName());
			pst.executeUpdate(); // 提交
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}finally{
			try {
				if(pst!=null){
					pst.close();
					
				}
				if(con!=null){
					con.close();
					
				}
			} catch (Exception e2) {
				e2.printStackTrace();
				return 0;
			}
		}
		return 1;
	}
	
	
	public List<UserBean> queryAllUsers() throws Exception {
		List<UserBean> list = new ArrayList<UserBean>();
		Connection con = DataAccess.getConnection();
		String sql = "select * from users where userID != '1' ";

		ResultSet rs = null;
		UserBean ub = null;
		Statement stmt = null;
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				ub = new UserBean();
				ub.setUserID(rs.getString("userID"));
				ub.setNickName(rs.getString("nickName"));
				ub.setUserName(rs.getString("userName"));
				ub.setPassword(rs.getString("password"));
				ub.setUserDescription(rs.getString("userDescription"));
				ub.setRoleID(rs.getString("roleID"));
				ub.setUserCreateTime(rs.getString("userCreateTime"));
				ub.setLoginTime(rs.getString("loginTime"));
				ub.setLoginTimeLong(rs.getString("loginTimeLong"));
				ub.setCurrentStatus(rs.getString("currentStatus"));
				ub.setIP(rs.getString("IP"));
				list.add(ub);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	
	public void delUserByUid(String uid) throws ClassNotFoundException, SQLException{
		
			Connection con = DataAccess.getConnection();
			
			String sql = "delete from users where userID=?";
			PreparedStatement pst = null;
			
			
			pst = con.prepareStatement(sql);
			pst.setString(1, uid);
			pst.executeUpdate();
			
				try{
					
					if(pst!=null){
						pst.close();
					}
					if(con!=null){
						con.close();
					}
				}catch (Exception e2){
					e2.printStackTrace();
				}
	   }
	
	public UserBean queryByUid(String uid) throws ClassNotFoundException{
		Connection con = DataAccess.getConnection();
		
		String sql = "select * from users where userID = ?";

		ResultSet rs = null;
		UserBean ub = null;
		PreparedStatement pst = null;
		
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, uid);
			
			rs = pst.executeQuery();

			if (rs.next()) {
				ub = new UserBean();
				ub.setUserID(uid);   //rs.getString("userID")
				ub.setNickName(rs.getString("nickName"));
				ub.setUserName(rs.getString("userName"));
				ub.setPassword(rs.getString("password"));
				ub.setUserDescription(rs.getString("userDescription"));
				ub.setRoleID(rs.getString("roleID"));
				ub.setUserCreateTime(rs.getString("userCreateTime"));
				ub.setLoginTime(rs.getString("loginTime"));
				ub.setLoginTimeLong(rs.getString("loginTimeLong"));
				ub.setCurrentStatus(rs.getString("currentStatus"));
				ub.setIP(rs.getString("IP"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(pst!=null){
					pst.close();
				}
				if(con!=null){
					con.close();
				}
			}catch (Exception e2){
				e2.printStackTrace();
			}
		}
		
		
		return ub;
		
	}


	public void updateUser(UserBean ub) throws ClassNotFoundException {
		// TODO 自动生成的方法存根
		Connection con = DataAccess.getConnection();  // 获取连接
		String sql = "update users set nickName=?,userName=?,password=?"
				+ ",userDescription=?,roleID=?,userCreateTime=?,loginTime=?,loginTimeLong=?"
				+ ",currentStatus=?,IP=?  where userID=?";  // 编写sql语句
		PreparedStatement pst = null; // 创建预处理语句
		try{
			//pst = con.prepareStatement(sql);  // 拿到预处理
			pst= con.prepareStatement(sql);
			pst.setString(1, ub.getNickName());
			pst.setString(2, ub.getUserName());
			pst.setString(3, ub.getPassword());
			pst.setString(4, ub.getUserDescription());
			pst.setString(5, ub.getRoleID());
			pst.setString(6, ub.getUserCreateTime());
			pst.setString(7, ub.getLoginTime());
			pst.setString(8, ub.getLoginTimeLong());
			pst.setString(9, ub.getCurrentStatus());
			pst.setString(10, ub.getIP());
			pst.setString(11, ub.getUserID());
			//pst.setString(5, ub.getRoleName());
			pst.executeUpdate(); // 提交
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(pst!=null){
					pst.close();
					
				}
				if(con!=null){
					con.close();
					
				}
			} catch (Exception e2) {
				e2.printStackTrace();
				
			}
		}
		
	}
	
}
